/**
 * 
 */
package vn.edu.fpt.xml.hrm.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.pojo.Project;

/**
 * @author Yoshimi
 *
 */
public class ProjectBean {

    private String dbms;
    private String serverName;
    private String port;
    private String dbName;
    private boolean integratedSecurity;
    private String username;
    private String password;

    /**
     *
     */
    public ProjectBean() {
        dbms = AppConstant.DB_DBMS;
        serverName = AppConstant.DB_SERVER_NAME;
        port = AppConstant.DB_PORT;
        dbName = AppConstant.DB_NAME;
        integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
        username = AppConstant.DB_USERNAME;
        password = AppConstant.DB_PASSWORD;
    }

    /**
     * @return the dbms
     */
    protected String getDbms() {
        return dbms;
    }

    /**
     * @param dbms the dbms to set
     */
    protected void setDbms(String dbms) {
        this.dbms = dbms;
    }

    /**
     * @return the serverName
     */
    protected String getServerName() {
        return serverName;
    }

    /**
     * @param serverName the serverName to set
     */
    protected void setServerName(String serverName) {
        this.serverName = serverName;
    }

    /**
     * @return the port
     */
    protected String getPort() {
        return port;
    }

    /**
     * @param port the port to set
     */
    protected void setPort(String port) {
        this.port = port;
    }

    /**
     * @return the dbName
     */
    protected String getDbName() {
        return dbName;
    }

    /**
     * @param dbName the dbName to set
     */
    protected void setDbName(String dbName) {
        this.dbName = dbName;
    }

    /**
     * @return the integratedSecurity
     */
    protected boolean isIntegratedSecurity() {
        return integratedSecurity;
    }

    /**
     * @param integratedSecurity the integratedSecurity to set
     */
    protected void setIntegratedSecurity(boolean integratedSecurity) {
        this.integratedSecurity = integratedSecurity;
    }

    /**
     * @return the username
     */
    protected String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    protected void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    protected String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    protected void setPassword(String password) {
        this.password = password;
    }

    private Connection connect() throws ClassNotFoundException, SQLException, NamingException {
        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();
        return con;
    }

    /**
     * @param fromDate
     * @param toDate
     * @param projectId
     * @param employeeId
     * @param status
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws DatatypeConfigurationException
     */
    public List<Project> getProjectList(int managerId) throws SQLException, ClassNotFoundException, DatatypeConfigurationException, NamingException {
        List<Project> result = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = null;
        Project project = null;
        try {
            connection = connect();

            // 2. create query
            // 2.1. create the query
            sql = "SELECT DISTINCT p.\"ProjectID\" AS \"id\""
                    + ", p.\"ProjectName\" AS \"name\""
                    + ", p.\"StartDate\" AS \"startDate\""
                    + ", p.\"EndDate\" AS \"endDate\""
                    + ", p.\"Status\" AS \"status\""
                    + " FROM \"Project\" AS p"
                    + " LEFT JOIN \"Role\" AS r ON p.\"ProjectID\" = r.\"ProjectID\""
                    + " WHERE r.\"EmployeeID\" = ? AND r.\"RoleTypeID\" = 3";
            // 2.2. turn the query to a preparedStatement
            statement = connection.prepareStatement(sql);
            // 2.3. add parameters to the statement
            statement.setInt(1, managerId);

            // 3. execute query
            resultSet = statement.executeQuery();

            // 4. read result
            result = new ArrayList<Project>();
            while (resultSet.next()) {
                project = new Project();
                project.setId(BigInteger.valueOf(resultSet.getInt("id")));
                project.setName(resultSet.getString("name"));
                project.setStartDate(FormatUtil.dateToCalendar(resultSet.getDate("startDate")));
                project.setEndDate(FormatUtil.dateToCalendar(resultSet.getDate("endDate")));
                project.setStatus(resultSet.getString("status"));
                result.add(project);
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return result;
    }

    public List<Project> getAllProject() throws SQLException {
        List<Project> list = new ArrayList<Project>();
        Connection connection = null;
        PreparedStatement stm = null;
        try {
            connection = connect();
            String sql = "SELECT * FROM Project";
            stm = connection.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                Project project = new Project();
                project.setId(BigInteger.valueOf(rs.getLong("ProjectID")));
                project.setName(rs.getString("ProjectName"));

                DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

                Date date = formatter.parse(rs.getString("StartDate"));
                GregorianCalendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                XMLGregorianCalendar xmlCalendar = DatatypeFactory.newInstance().
                        newXMLGregorianCalendar(calendar);
                project.setStartDate(FormatUtil.dateToCalendar(rs.getDate("StartDate")));

                Date date2 = formatter.parse(rs.getString("EndDate"));
                calendar = new GregorianCalendar();
                calendar.setTime(date2);
                xmlCalendar = DatatypeFactory.newInstance().newXMLGregorianCalendar(calendar);
                project.setEndDate(FormatUtil.dateToCalendar(rs.getDate("EndDate")));

                project.setStatus(rs.getString("Status"));

                list.add(project);
            }

        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(ProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(ProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NamingException ex) {
            Logger.getLogger(ProjectBean.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (connection != null) {
                connection.close();
            }
            if (stm != null) {
                stm.close();
            }
        }

        return list;
    }
}
